package cn.javaxc.mvcwork.util.common;
import cn.javaxc.mvcwork.controller.*;
import cn.javaxc.mvcwork.controller.web.PagedListInfo;
import cn.javaxc.mvcwork.framework.actions.TActionContext;
import cn.javaxc.mvcwork.framework.database.*;
import cn.javaxc.model.base.ISQLException;
import java.io.*;
import java.net.URL;
import java.sql.*;
import java.text.*;
import java.util.*;
import javax.servlet.ServletContext;
import org.apache.log4j.Logger;
import java.util.Date;




public class DatabaseUtils {

  private static Logger log = Logger.getLogger(DatabaseUtils.class);
  public final static String CRLF = System.getProperty("line.separator");

  //Possible time in milies for SQL query
  public static final long POSSIBLE_QUERY_TIME = 3000;

  // Quote symbols
  public final static String qsDefault = "\"";
  public final static String qsMySQL = "`";

  // Database types
  public final static int POSTGRESQL = 1;
  public final static int MSSQL = 2;
  public final static int ORACLE = 3;
  public final static int FIREBIRD = 4;
  public final static int DAFFODILDB = 5;
  public final static int DB2 = 6;
  public final static int MYSQL = 7;
  public final static int DERBY = 8;
  public final static int INTERBASE = 9;
  public final static String sqlReservedWords = ",language,password,level,type,position,second," +
      "minute,hour,month,dayofweek,year,length,message," +
      "active,role,number,module,section,value,size," +
      "version,display,parameter,action,global,account,lock,comment," +
      "offset,";
  //intervals
  public final static int DAY = 1;
  public final static int WEEK = 2;
  public final static int MONTH = 3;
  public final static int YEAR = 4;
  public final static int HOUR = 5;
  public final static int MINUTE = 6;
  public final static int SECOND = 7;


  
  public static String getTrue(Connection db) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "true";
      case DatabaseUtils.MSSQL:
        return "1";
      case DatabaseUtils.DAFFODILDB:
        return "true";
      case DatabaseUtils.ORACLE:
        return "1";
      case DatabaseUtils.DB2:
        return "'1'";
      case DatabaseUtils.FIREBIRD:
        return "'Y'";
      case DatabaseUtils.MYSQL:
        return "1";
      case DatabaseUtils.DERBY:
        return "'1'";
      case DatabaseUtils.INTERBASE:
    	  return "true";
      default:
        return "true";
    }
  }


  
  public static String getFalse(Connection db) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "false";
      case DatabaseUtils.MSSQL:
        return "0";
      case DatabaseUtils.DAFFODILDB:
        return "false";
      case DatabaseUtils.ORACLE:
        return "0";
      case DatabaseUtils.DB2:
        return "'0'";
      case DatabaseUtils.FIREBIRD:
        return "'N'";
      case DatabaseUtils.MYSQL:
        return "0";
      case DatabaseUtils.DERBY:
        return "'0'";
      case DatabaseUtils.INTERBASE:
        return "false";
      default:
        return "false";
    }
  }


  
  public static String getCurrentTimestamp(Connection db) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.MSSQL:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.DAFFODILDB:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.ORACLE:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.DB2:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.FIREBIRD:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.MYSQL:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.DERBY:
        return "CURRENT_TIMESTAMP";
      case DatabaseUtils.INTERBASE:
        return "CURRENT_TIMESTAMP";
      default:
        return "CURRENT_TIMESTAMP";
    }
  }


  
  public static int getType(Connection db){
	  return MVCConfig.getConfig().getDBType(db);
  }
  public static int getDBType(String databaseName) {
    if (databaseName.indexOf("postgresql") > -1) {
      return POSTGRESQL;
    } else if ("net.sourceforge.jtds.jdbc.ConnectionJDBC3".equals(databaseName)) {
      return MSSQL;
    } else if ("net.sourceforge.jtds.jdbc.TdsConnectionJDBC3".equals(databaseName)) {
      return MSSQL;
    } else if ("net.sourceforge.jtds.jdbc.TdsConnection".equals(databaseName)) {
      return MSSQL;
    } else if ("org.firebirdsql.jdbc.FBConnection".equals(databaseName)) {
      return FIREBIRD;
    } else if ("org.firebirdsql.jdbc.FBDriver".equals(databaseName)) {
      return FIREBIRD;
    } else if ("oracle.jdbc.driver.OracleConnection".equals(databaseName)) {
      return ORACLE;
    } else if ("in.co.daffodil.db.jdbc.DaffodilDBConnection".equals(databaseName)) {
      return DAFFODILDB;
    } else if ("interbase.interclient.Connection".equals(databaseName)) {
      return INTERBASE;
    } else if (databaseName.indexOf("sqlserver") > -1) {
      return MSSQL;
    } else if (databaseName.indexOf("oracle") > -1) {
      return ORACLE;
    } else if (databaseName.indexOf("db2") > -1) {
      return DB2;
    } else if (databaseName.indexOf("mysql") > -1) {
      return MYSQL;
    } else if (databaseName.indexOf("derby") > -1) {
      return DERBY;
    } else {
      System.err.println("DatabaseUtils-> Unkown Connection Class: " + databaseName);
      return -1;
    }
  }
  public static int getDrive(String databaseName) {
	if(databaseName==null)return -1;
	if (databaseName.indexOf("net.sourceforge.jtds.jdbc") > -1){
	  return MSSQL;
	} else if (databaseName.indexOf("sqlserver") > -1) {
      return MSSQL;
    } else if (databaseName.indexOf("oracle") > -1) {
      return ORACLE;
    } else if (databaseName.indexOf("db2") > -1) {
      return DB2;
    } else if (databaseName.indexOf("mysql") > -1) {
      return MYSQL;
    } else if (databaseName.indexOf("derby") > -1) {
      return DERBY;
    } else {
      return -1;
    }
  }

  
  public static String getTypeName(Connection db) {
    switch (getType(db)) {
      case POSTGRESQL:
        return "postgresql";
      case MSSQL:
        return "mssql";
      case FIREBIRD:
        return "firebird";
      case ORACLE:
        return "oracle";
      case DAFFODILDB:
        return "daffodildb";
      case DB2:
        return "db2";
      case MYSQL:
        return "mysql";
      case DERBY:
        return "derby";
      case INTERBASE:
        return "interbase";
      default:
        return "unknown";
    }
  }


  
  public static String castDateTimeToDate(Connection db, String date) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return (date + "::date");
      case DatabaseUtils.MSSQL:
        return ("CONVERT(char(10), " + date + ", 101)");
      case DatabaseUtils.FIREBIRD:
        return ("EXTRACT(DATE FROM " + date + ")");
      case DatabaseUtils.DAFFODILDB:
        return ("DATE(" + date + ")");
      case DatabaseUtils.ORACLE:
        return ("TO_DATE(" + date + ",'dd/mm/yyyy')");
        //case DatabaseUtils.ORACLE:
        //  return ("CAST(" + date + " AS DATE)");
      case DatabaseUtils.DB2:
        return ("CAST(" + date + " AS DATE)");
        //case DatabaseUtils.ORACLE:
        //  return ("CAST(" + date + " AS DATE)");
      case DatabaseUtils.MYSQL:
        return ("DATE(" + date + ")");
      case DatabaseUtils.DERBY:
        return ("DATE(" + date + ")");
      case DatabaseUtils.INTERBASE:
        return ("CAST(" + date + " AS DATE)");
      default:
        return "";
    }
  }


  
  public static String addTimestampInterval(Connection db, int units, String termsColumnName, String timestampColumnName) {
    // TODO: report why +1 is being used
    String addTimestampIntervalString = "";
    String customUnits = "";
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        if (units == DAY) {
          customUnits = "days";
        } else if (units == WEEK) {
          customUnits = "weeks";
        } else if (units == MONTH) {
          customUnits = "months";
        } else if (units == YEAR) {
          customUnits = "years";
        }
        addTimestampIntervalString = timestampColumnName + " + ( (" + termsColumnName + " + 1 )::text || ' " + customUnits + "')::interval ";
        break;
      case DatabaseUtils.MSSQL:
        if (units == DAY) {
        } else if (units == WEEK) {
          customUnits = "WEEK";
        } else if (units == MONTH) {
          customUnits = "MONTH";
        } else if (units == YEAR) {
          customUnits = "YEAR";
        }
        addTimestampIntervalString = " DATEADD(" + customUnits + ",(" + termsColumnName + " + 1)," + timestampColumnName + ")";
        break;
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        if (units == DAY) {
          addTimestampIntervalString = " (" + timestampColumnName + " + " + termsColumnName + ") ";
        } else if (units == WEEK) {
          addTimestampIntervalString = " (" + timestampColumnName + " + (" + termsColumnName + " * 7)) ";
        } else if (units == MONTH) {
          // NOTE: approximate function to add a month
          addTimestampIntervalString = " (" + timestampColumnName + " + (" + termsColumnName + " * 30)) ";
        } else if (units == YEAR) {
          // NOTE: approximate function to add a year
          addTimestampIntervalString = " (" + timestampColumnName + " + (" + termsColumnName + " * 365)) ";
        }
        break;
      case DatabaseUtils.DAFFODILDB:
        if (units == DAY) {
        } else if (units == WEEK) {
          customUnits = "SQL_TSI_WEEK";
        } else if (units == MONTH) {
          customUnits = "SQL_TSI_MONTH";
        } else if (units == YEAR) {
          customUnits = "SQL_TSI_YEAR";
        }
        addTimestampIntervalString = " TIMESTAMPADD(" + customUnits + ",(" + termsColumnName + " + 1)," + timestampColumnName + ")";
        break;
      case DatabaseUtils.DB2:
        if (units == DAY) {
          addTimestampIntervalString = timestampColumnName + " + (" + termsColumnName + "+1) day ";
        } else if (units == WEEK) {
          addTimestampIntervalString = timestampColumnName + " + ((" + termsColumnName + "+1)*7) day ";
        } else if (units == MONTH) {
          addTimestampIntervalString = timestampColumnName + " + (" + termsColumnName + "+1) month ";
        } else if (units == YEAR) {
          addTimestampIntervalString = timestampColumnName + " + (" + termsColumnName + "+1) year ";
        }
        break;
      case DatabaseUtils.ORACLE:
        if (units == DAY) {
          addTimestampIntervalString = " (" + timestampColumnName + " + NUMTODSINTERVAL(" + termsColumnName + " ,'day')) ";
        } else if (units == WEEK) {
          addTimestampIntervalString = " (" + timestampColumnName + " + NUMTODSINTERVAL(" + termsColumnName + " * 7 ,'day'))";
        } else if (units == MONTH) {
          addTimestampIntervalString = " (" + timestampColumnName + " + NUMTOYMINTERVAL(" + termsColumnName + ", 'month')) ";
        } else if (units == YEAR) {
          addTimestampIntervalString = " (" + timestampColumnName + " + NUMTOYMINTERVAL(" + termsColumnName + ", 'year')) ";
        }
        break;
      case DatabaseUtils.MYSQL:
        if (units == DAY) {
        } else if (units == WEEK) {
          customUnits = "WEEK";
        } else if (units == MONTH) {
          customUnits = "MONTH";
        } else if (units == YEAR) {
          customUnits = "YEAR";
        }
        addTimestampIntervalString = " ADDDATE(" + timestampColumnName + ", INTERVAL " + termsColumnName + "+1 " + customUnits + ")";
        break;
      case DatabaseUtils.DERBY:
        if (units == DAY) {
          customUnits = "SQL_TSI_DAY";
        } else if (units == WEEK) {
          customUnits = "SQL_TSI_WEEK";
        } else if (units == MONTH) {
          customUnits = "SQL_TSI_MONTH";
        } else if (units == YEAR) {
          customUnits = "SQL_TSI_YEAR";
        }
        addTimestampIntervalString = " {fn TIMESTAMPADD(" + customUnits + ", CAST(" + termsColumnName + "+1 AS INTEGER), " + timestampColumnName + ")}";
        break;
    }
    return addTimestampIntervalString;
  }


  
  public static String addTimestampInterval(Connection db, int units, String termsColumnName, String timestampColumnName, String defaultUnits, long defaultTerms) {
    String addTimestampIntervalString = "";
    String customUnits = "";
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        if (units == WEEK) {
          customUnits = "weeks";
        }
        addTimestampIntervalString = timestampColumnName + " + ( (" + termsColumnName + " + " + (defaultTerms + 1) + " )::text || ' " + customUnits + "')::interval ";
        break;
      case DatabaseUtils.MSSQL:
        if (units == WEEK) {
          customUnits = "WEEK";
        }
        addTimestampIntervalString = " DATEADD(" + customUnits + ",(" + termsColumnName + " + " + (defaultTerms + 1) + ")," + timestampColumnName + ")";
        break;
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        addTimestampIntervalString = " (" + timestampColumnName + " + ((" + termsColumnName + " + " + defaultTerms + 1 + ") * 7)) ";
        break;
      case DatabaseUtils.DAFFODILDB:
        if (units == WEEK) {
          customUnits = "SQL_TSI_WEEK";
        }
        addTimestampIntervalString = " TIMESTAMPADD(" + customUnits + ",(" + termsColumnName + " + " + (defaultTerms + 1) + ")," + timestampColumnName + ")";
        break;
      case DatabaseUtils.DB2:
        if (units == WEEK) {
          addTimestampIntervalString = timestampColumnName + " + ((" + termsColumnName + "+" + defaultTerms + "+1)*7) day ";
        }
        break;
      case DatabaseUtils.ORACLE:
        addTimestampIntervalString = " (" + timestampColumnName + " + ((" + termsColumnName + " + " + defaultTerms + 1 + ") * 7)) ";
        break;
      case DatabaseUtils.MYSQL:
        if (units == WEEK) {
          customUnits = "WEEK";
        }
        addTimestampIntervalString = " ADDDATE(" + timestampColumnName + ", INTERVAL (" + termsColumnName + " + " + (defaultTerms + 1) + ") " + customUnits + ")";
        break;
      case DatabaseUtils.DERBY:
        if (units == WEEK) {
          customUnits = "SQL_TSI_WEEK";
        }
        addTimestampIntervalString = "{fn TIMESTAMPADD(" + customUnits + ", CAST(" + termsColumnName + " + " + (defaultTerms + 1) + " AS INTEGER)," + timestampColumnName + ")}";
        break;
    }
    return addTimestampIntervalString;
  }


  


  public static String getSequenceName(Connection db, String sequenceName) {
    int typeId = DatabaseUtils.getType(db);
    switch (typeId) {
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        // interbase actually allows 64 character names, but since we are using the same db scripts...
        if (sequenceName.length() > 31) {
          String seqPart1 = sequenceName.substring(0, 13);
          String seqPart2 = sequenceName.substring(14);
          sequenceName = seqPart1 + "_" + seqPart2.substring(seqPart2.length() - 17);
        }
        break;
      case DatabaseUtils.DAFFODILDB:
        break;
      case DatabaseUtils.ORACLE:
        if (sequenceName.length() > 30) {
          String seqPart1 = sequenceName.substring(0, 13);
          String seqPart2 = sequenceName.substring(14);
          sequenceName = seqPart1 + "_" + seqPart2.substring(seqPart2.length() - 16);
        }
        break;
      case DatabaseUtils.DB2:
        if (sequenceName.length() > 30) {
          String seqPart1 = sequenceName.substring(0, 13);
          String seqPart2 = sequenceName.substring(14);
          sequenceName = seqPart1 + "_" + seqPart2.substring(seqPart2.length() - 16);
        }
        break;
      default:
        break;
    }
    return sequenceName;
  }


  
  public static int getCurrVal(Connection db, String sequenceName, int defaultValue){
	  return -1;
  }


  
  public static String getYearPart(Connection db, String fieldname) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "date_part('year', " + fieldname + ")";
      case DatabaseUtils.MSSQL:
        return "DATEPART(YY, " + fieldname + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "EXTRACT(YEAR FROM " + fieldname + ")";
      case DatabaseUtils.DAFFODILDB:
        return "YEAR(" + fieldname + ")";
      case DatabaseUtils.ORACLE:
        return "EXTRACT(YEAR FROM " + fieldname + ")";
      case DatabaseUtils.DB2:
        return "YEAR(" + fieldname + ")";
      case DatabaseUtils.MYSQL:
        return "YEAR(" + fieldname + ")";
      case DatabaseUtils.DERBY:
        return "YEAR(" + fieldname + ")";
      default:
        return "";
    }
  }


  
  public static String getTempTableName(Connection db, String tableName) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return tableName;
      case DatabaseUtils.MSSQL:
        return "#" + tableName;
      case DatabaseUtils.FIREBIRD:
        return "";
      case DatabaseUtils.DAFFODILDB:
        return "";
      case DatabaseUtils.ORACLE:
        return "";
      case DatabaseUtils.DB2:
        return "SESSION." + tableName;
      case DatabaseUtils.MYSQL:
        return "";
      case DatabaseUtils.DERBY:
        return "";
      case DatabaseUtils.INTERBASE:
        // ib does support temporary tables?
        return "";
      default:
        return "";
    }
  }

  
  public static String getMonthPart(Connection db, String fieldname) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "date_part('month', " + fieldname + ")";
      case DatabaseUtils.MSSQL:
        return "DATEPART(MM, " + fieldname + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "EXTRACT(MONTH FROM " + fieldname + ")";
      case DatabaseUtils.DAFFODILDB:
        return "MONTH(" + fieldname + ")";
      case DatabaseUtils.ORACLE:
        return "EXTRACT(MONTH FROM " + fieldname + ")";
      case DatabaseUtils.DB2:
        return "MONTH(" + fieldname + ")";
      case DatabaseUtils.MYSQL:
        return "MONTH(" + fieldname + ")";
      case DatabaseUtils.DERBY:
        return "MONTH(" + fieldname + ")";
      default:
        return "";
    }
  }


  
  public static String getDayPart(Connection db, String fieldname) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "date_part('day', " + fieldname + ")";
      case DatabaseUtils.MSSQL:
        return "DATEPART(DD, " + fieldname + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "EXTRACT(DAY FROM " + fieldname + ")";
      case DatabaseUtils.DAFFODILDB:
        return "DAYOFWEEK(" + fieldname + ")";
      case DatabaseUtils.ORACLE:
        return "EXTRACT(DAY FROM " + fieldname + ")";
      case DatabaseUtils.DB2:
        return "DAY(" + fieldname + ")";
      case DatabaseUtils.MYSQL:
        return "DAY(" + fieldname + ")";
      case DatabaseUtils.DERBY:
        return "DAY(" + fieldname + ")";
      default:
        return "";
    }
  }


  
  public static String getHourPart(Connection db, String fieldname) {
    //TODO: Verify if all databases work
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "date_part('hour', " + fieldname + ")";
      case DatabaseUtils.MSSQL:
        return "DATEPART(HH, " + fieldname + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "EXTRACT(HOUR FROM " + fieldname + ")";
      case DatabaseUtils.DAFFODILDB:
        //return "DAYOFWEEK(" + fieldname + ")";
      case DatabaseUtils.ORACLE:
        return "EXTRACT(HOUR FROM " + fieldname + ")";
      case DatabaseUtils.DB2:
        return "HOUR(" + fieldname + ")";
      case DatabaseUtils.MYSQL:
        return "HOUR(" + fieldname + ")";
      case DatabaseUtils.DERBY:
        return "HOUR(" + fieldname + ")";
      default:
        return "";
    }
  }


  
  public static String getMinutePart(Connection db, String fieldname) {
    //TODO: Verify if all databases work
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "date_part('minute', " + fieldname + ")";
      case DatabaseUtils.MSSQL:
        return "DATEPART(M, " + fieldname + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "EXTRACT(MINUTE FROM " + fieldname + ")";
      case DatabaseUtils.DAFFODILDB:
        return "DAYOFWEEK(" + fieldname + ")";
      case DatabaseUtils.ORACLE:
        return "EXTRACT(MINUTE FROM " + fieldname + ")";
      case DatabaseUtils.DB2:
        return "MINUTE(" + fieldname + ")";
      case DatabaseUtils.MYSQL:
        return "MINUTE(" + fieldname + ")";
      case DatabaseUtils.DERBY:
        return "MINUTE(" + fieldname + ")";
      default:
        return "";
    }
  }


  
  public static String toLowerCase(Connection db) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "lower";
      case DatabaseUtils.MSSQL:
        return "lower";
      case DatabaseUtils.ORACLE:
        return "lower";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "lower";
      case DatabaseUtils.DAFFODILDB:
        return "lcase";
      case DatabaseUtils.DB2:
        return "lower";
      case DatabaseUtils.MYSQL:
        return "lower";
      case DatabaseUtils.DERBY:
        return "lcase";
      default:
        return "lower";
    }
  }


  
  public static String toLowerCase(Connection db, String field) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "lower(" + field + ")";
      case DatabaseUtils.MSSQL:
        return "lower(" + field + ")";
      case DatabaseUtils.ORACLE:
        return "lower(" + field + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return "lower(" + field + ")";
      case DatabaseUtils.DAFFODILDB:
        return "lcase(" + field + ")";
      case DatabaseUtils.DB2:
        return "lower(" + field + ")";
      case DatabaseUtils.MYSQL:
        return "lower(" + field + ")";
      case DatabaseUtils.DERBY:
        return "LCASE(" + field + ")";
      default:
        return "lower(" + field + ")";
    }
  }


  
  public static String getSubString(Connection db, String field, int first, int size) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return "substr(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.MSSQL:
        return "substring(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.ORACLE:
        return "substr(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.FIREBIRD:
        return "substr(" + field + " FROM " + first + (size < 0 ? "" : " FOR " + size) + " ) ";
      case DatabaseUtils.DAFFODILDB:
        return "substring(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.DB2:
        return "substr(" + field + "," + (first + 1) + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.MYSQL:
        return "substr(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.DERBY:
        return "substr(" + field + "," + (first + 1) + (size < 0 ? "" : "," + size) + ") ";
      case DatabaseUtils.INTERBASE:
        return "substr(" + field + "," + (first + 1) + (size < 0 ? ", 32767" : "," + size) + " )";
      default:
        return "substr(" + field + "," + first + (size < 0 ? "" : "," + size) + ") ";
    }
  }


  
  public static String convertToVarChar(Connection db, String field) {
    switch (DatabaseUtils.getType(db)) {
      case DatabaseUtils.POSTGRESQL:
        return field;
      case DatabaseUtils.MSSQL:
        return "CONVERT(VARCHAR(2000), " + field + ")";
      case DatabaseUtils.ORACLE:
        return "TO_CHAR(" + field + ")";
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        return field;
      case DatabaseUtils.DB2:
        return "CAST(" + field + " AS VARCHAR(32000))";
      case DatabaseUtils.DAFFODILDB:
        // TODO: This doesn't work for DaffodilDB, so use a VARCHAR(4192) instead of CLOB
        return field;
      case DatabaseUtils.MYSQL:
        // NOTE: If MYSQL has a problem then get a substring
        //return "SUBSTR(" + field + ", 2000, 1)";
        return field;
      case DatabaseUtils.DERBY:
        return "CAST(" + field + " AS VARCHAR(32000))";
      default:
        return field;
    }

  }


  
  public static int parseInt(String tmp, int defaultValue) {
    try {
      return Integer.parseInt(tmp);
    } catch (Exception e) {
      return defaultValue;
    }
  }


  
  public static boolean parseBoolean(String tmp) {
    return ("ON".equalsIgnoreCase(tmp) ||
        "TRUE".equalsIgnoreCase(tmp) ||
        "1".equals(tmp) ||
        "Y".equalsIgnoreCase(tmp) ||
        "YES".equalsIgnoreCase(tmp));
  }


  
  public static java.sql.Date parseDate(String tmp) {
    java.sql.Date dateValue = null;
    try {
      java.util.Date tmpDate = DateFormat.getDateInstance(DateFormat.SHORT).parse(
          tmp);
      dateValue = new java.sql.Date(new java.util.Date().getTime());
      dateValue.setTime(tmpDate.getTime());
      return dateValue;
    } catch (Exception e) {
      try {
        return java.sql.Date.valueOf(tmp);
      } catch (Exception e2) {
      }
    }
    return null;
  }


  
  public static java.sql.Timestamp parseTimestamp(String tmp) {
    return parseTimestamp(tmp, Locale.getDefault());
  }


  
  public static java.sql.Timestamp parseTimestamp(String tmp, Locale locale) {
    java.sql.Timestamp timestampValue = null;
    try {
      java.util.Date tmpDate = DateFormat.getDateTimeInstance(
          DateFormat.SHORT, DateFormat.LONG, locale).parse(tmp);
      timestampValue = new java.sql.Timestamp(new java.util.Date().getTime());
      timestampValue.setTime(tmpDate.getTime());
      return timestampValue;
    } catch (Exception e) {
      try {
        return java.sql.Timestamp.valueOf(tmp);
      } catch (Exception e2) {
      }
    }
    return null;
  }


  
  public static java.sql.Timestamp parseTimestamp(String tmp, Locale locale, boolean beLenient) {
    java.sql.Timestamp timestampValue = null;
    try {
      SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM/dd/yyyy", locale);
      simpleDateFormat.setLenient(beLenient);
      java.util.Date tmpDate = simpleDateFormat.parse(tmp);
      timestampValue = new java.sql.Timestamp(new java.util.Date().getTime());
      timestampValue.setTime(tmpDate.getTime());
      return timestampValue;
    } catch (Exception e) {
      try {
        return java.sql.Timestamp.valueOf(tmp);
      } catch (Exception e2) {
      }
    }
    return null;
  }


  
  public static java.sql.Timestamp parseDateToTimestamp(String tmp) {
    return parseDateToTimestamp(tmp, Locale.getDefault());
  }


  
  public static java.sql.Timestamp parseDateToTimestamp(String tmp, Locale locale) {
    java.sql.Timestamp timestampValue = DatabaseUtils.parseTimestamp(
        tmp, locale);
    if (timestampValue == null) {
      try {
        DateFormat tmpDateFormat = DateFormat.getDateInstance(
            DateFormat.SHORT, locale);
        tmpDateFormat.setLenient(false);
        java.util.Date tmpDate = tmpDateFormat.parse(tmp);
        timestampValue = new java.sql.Timestamp(System.currentTimeMillis());
        timestampValue.setTime(tmpDate.getTime());
        timestampValue.setNanos(0);
        return timestampValue;
      } catch (Exception e) {
      }
    }
    return timestampValue;
  }


  
  public static int getInt(ResultSet rs, String column, int defaultValue) throws SQLException {
    int fieldValue = rs.getInt(column);
    if (rs.wasNull()) {
      fieldValue = defaultValue;
    }
    return fieldValue;
  }


  
  public static double getDouble(ResultSet rs, String column, double defaultValue) throws SQLException {
    double fieldValue = rs.getDouble(column);
    if (rs.wasNull()) {
      fieldValue = defaultValue;
    }
    return fieldValue;
  }


  
  public static int getInt(ResultSet rs, String column) throws SQLException {
    return DatabaseUtils.getInt(rs, column, -1);
  }


  
  public static double getDouble(ResultSet rs, String column) throws SQLException {
    return DatabaseUtils.getDouble(rs, column, -1.0);
  }


  
  public static long getLong(ResultSet rs, String column) throws SQLException {
    return DatabaseUtils.getLong(rs, column, -1);
  }


  
  public static long getLong(ResultSet rs, String column, long defaultValue) throws SQLException {
    long fieldValue = rs.getLong(column);
    if (rs.wasNull()) {
      fieldValue = defaultValue;
    }
    return fieldValue;
  }


  
  public static void setInt(PreparedStatement pst, int paramCount, int value) throws SQLException {
    if (value == -1) {
      pst.setNull(paramCount, java.sql.Types.INTEGER);
    } else {
      pst.setInt(paramCount, value);
    }
  }


  
  public static void setDouble(PreparedStatement pst, int paramCount, double value) throws SQLException {
    if (value == -1.0) {
      pst.setNull(paramCount, java.sql.Types.DOUBLE);
    } else {
      pst.setDouble(paramCount, value);
    }
  }


  
  public static void setLong(PreparedStatement pst, int paramCount, long value) throws SQLException {
    if (value == -1) {
      pst.setNull(paramCount, java.sql.Types.INTEGER);
    } else {
      pst.setLong(paramCount, value);
    }
  }


  
  public static void setTimestamp(PreparedStatement pst, int paramCount, java.sql.Timestamp value) throws SQLException {
    if (value == null) {
      pst.setNull(paramCount, java.sql.Types.DATE);
    } else {
      pst.setTimestamp(paramCount, value);
    }
  }


  
  public static void setDate(PreparedStatement pst, int paramCount, java.sql.Date value) throws SQLException {
    if (value == null) {
      pst.setNull(paramCount, java.sql.Types.DATE);
    } else {
      pst.setDate(paramCount, value);
    }
  }


  
  public static void executeSQL(Connection db, String filename) throws SQLException, IOException {
    System.out.println("DatabaseUtils-> executeSQL: " + filename);
    BufferedReader in = new BufferedReader(new FileReader(filename));
    executeSQL(db, in);
    in.close();
  }


  
  public static void executeSQL(Connection db, ServletContext context, String filename) throws SQLException, IOException {
    if (context == null) {
      DatabaseUtils.executeSQL(db, filename);
    } else {
      InputStream source = context.getResourceAsStream(filename);
      BufferedReader in = new BufferedReader(new InputStreamReader(source));
      executeSQL(db, in);
      in.close();
    }
  }

  public static void executeSQL(Connection db, URL url) throws SQLException, IOException {
    InputStream source = url.openStream();
    BufferedReader in = new BufferedReader(new InputStreamReader(source));
    executeSQL(db, in);
    in.close();
  }


  
  public static void executeSQL(Connection db, BufferedReader in) throws SQLException, IOException {
    // Read the file and execute each statement
    StringBuffer sql = new StringBuffer();
    String line = null;
    Statement st = db.createStatement();
    int tCount = 0;
    int lineCount = 0;
    while ((line = in.readLine()) != null) {
      ++lineCount;
      // SQL Comment
      if (line.startsWith("//")) {
        continue;
      }
      // SQL Comment
      if (line.startsWith("--")) {
        continue;
      }
      sql.append(line);
      // check for delimiter
      if (line.trim().endsWith(";")) {
        // Got a transaction, so execute it
        ++tCount;
        try {
          st.execute(sql.substring(0, sql.lastIndexOf(";")));
        } catch (SQLException e) {
          System.out.println(
              "DatabaseUtils-> ERROR(1), line: " + lineCount + " message: " + e.getMessage());
          throw new SQLException(e.getMessage());
        }
        sql.setLength(0);
      } else if (line.trim().equals("GO")) {
        // Got a transaction, so execute it
        ++tCount;
        try {
          st.execute(sql.substring(0, sql.lastIndexOf("GO")));
        } catch (SQLException e) {
          System.out.println(
              "DatabaseUtils-> ERROR(2), line: " + lineCount + " message: " + e.getMessage());
          throw new SQLException(e.getMessage());
        }
        sql.setLength(0);
      } else {
        // Continue with another line
        sql.append(CRLF);
      }
    }
    // Statement didn't end with a delimiter
    if (sql.toString().trim().length() > 0 && !CRLF.equals(
        sql.toString().trim())) {
      ++tCount;
      try {
        st.execute(sql.toString());
      } catch (SQLException e) {
        System.out.println(
            "DatabaseUtils-> ERROR(3), line: " + lineCount + " message: " + e.getMessage());
        throw new SQLException(e.getMessage());
      }
    }
    st.close();
    System.out.println("Executed " + tCount + " total statements");
  }



  
  public static String parseReservedWord(Connection db, String reservedWord) {
    if (DatabaseUtils.getType(db) == FIREBIRD ||
        DatabaseUtils.getType(db) == INTERBASE ||
        DatabaseUtils.getType(db) == ORACLE ||
        DatabaseUtils.getType(db) == DB2 ||
        DatabaseUtils.getType(db) == MYSQL ||
        DatabaseUtils.getType(db) == DERBY) {
      if (reservedWord.indexOf(".") > -1) {
        // Table is being specified with field
        String part1 = reservedWord.substring(0, reservedWord.indexOf("."));
        String part2 = reservedWord.substring(reservedWord.indexOf(".") + 1);
        return (part1 + "." + parseReservedWord(db, part2));
      }
      if (sqlReservedWords.indexOf("," + reservedWord + ",") != -1) {
        return addQuotes(db, reservedWord);
      }
    }
    return reservedWord;
  }


  
  private static String getQuote(Connection db) {
    String quoteSymbol = "";
    switch (DatabaseUtils.getType(db)) {
      case MYSQL:
        quoteSymbol = qsMySQL;
        break;
      default:
        quoteSymbol = qsDefault;
    }

    return quoteSymbol;
  }


  
  public static String addQuotes(Connection db, String stringToQuote) {
    String quoteSymbol = getQuote(db);
    return quoteSymbol + stringToQuote + quoteSymbol;
  }


  
  public static synchronized Connection getConnection(String dbUrl, String dbUser, String dbPwd) throws SQLException {
    Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
    /*
    System.out.println("DatabaseUtils-> Holdability: " + connection.getHoldability());
    */
    if (DatabaseUtils.getType(connection) == DatabaseUtils.MYSQL) {
      PreparedStatement pst = connection.prepareStatement("SELECT @@session.sql_mode;");
      String currentMode = "";
      ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        currentMode = rs.getString(1);
      }
      rs.close();
      pst.close();

      pst = connection.prepareStatement(
          "SET sql_mode = '" + currentMode + ((currentMode.length() > 0) ? "," : "") + "ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO';");
      pst.execute();
      pst.close();
    } else if (DatabaseUtils.getType(connection) == DatabaseUtils.DB2) {
      /*
      connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
      */
    }
    return connection;
  }


  
  public static void skipRowsManual(Connection db, ResultSet rs, int skipRowsCount) throws SQLException {
	if (MVCConfig.getConfig().getDBPage()==DatabaseUtils.MSSQL){
		rs.absolute(skipRowsCount); 
	}else if (DatabaseUtils.getType(db) == DatabaseUtils.MSSQL ||
		DatabaseUtils.getType(db) == DatabaseUtils.MYSQL ||
        DatabaseUtils.getType(db) == DatabaseUtils.DAFFODILDB ||
        DatabaseUtils.getType(db) == DatabaseUtils.DERBY ||
        DatabaseUtils.getType(db) == DatabaseUtils.ORACLE) {
        for (int skipCount = 0; skipCount < skipRowsCount; skipCount++) {
			rs.next();
        }
    }
  }

  
  public static void doManualLimit(Connection db, PreparedStatement pst, int maxRowsCount) throws SQLException {
	pst.setMaxRows(maxRowsCount);
  }


  public static String getTruncDateDialect(String dateColumn, int truncTo, int dbType) {
    String truncSQL = "";
    String dateFormat = "";
    switch (dbType) {
      case DatabaseUtils.POSTGRESQL:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            dateFormat = "day";
            break;
          case DatabaseUtils.MONTH:
            dateFormat = "month";
            break;
          case DatabaseUtils.MINUTE:
            dateFormat = "minute";
            break;
          case DatabaseUtils.HOUR:
            dateFormat = "hour";
            break;
          case DatabaseUtils.YEAR:
            dateFormat = "year";
            break;
          default:
            return null;
        }
        truncSQL = "date_trunc('" + dateFormat + "'," + dateColumn + ")";
        break;
      case DatabaseUtils.MSSQL:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            truncSQL = "CAST(CONVERT(varchar, " + dateColumn + ",101) AS DATETIME) ";
            break;
          case DatabaseUtils.MONTH:
            // TODO: fix this
            truncSQL = "CAST(CONVERT(varchar, " + dateColumn + ",101) AS DATETIME) ";
            break;
          case DatabaseUtils.MINUTE:
            truncSQL = "CAST(CONVERT(varchar, " + dateColumn + ",100) AS DATETIME) ";
            break;
          case DatabaseUtils.HOUR:
            // TODO: fix this
            truncSQL = "CAST(CONVERT(varchar, " + dateColumn + ",101) AS DATETIME) ";
            break;
          case DatabaseUtils.YEAR:
            // TODO: fix this
            truncSQL = "CAST(CONVERT(varchar, " + dateColumn + ",101) AS DATETIME) ";
            break;
          default:
            return null;
        }
        break;
      case DatabaseUtils.DAFFODILDB:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            dateFormat = "DD";
            break;
          case DatabaseUtils.MONTH:
            dateFormat = "MM";
            break;
          case DatabaseUtils.MINUTE:
            dateFormat = "MI";
            break;
          case DatabaseUtils.HOUR:
            dateFormat = "HH";
            break;
          case DatabaseUtils.YEAR:
            dateFormat = "YYYY";
            break;
          default:
            return null;
        }
        truncSQL = "trunc(" + dateColumn + ",'" + dateFormat + "')";
        break;
      case DatabaseUtils.ORACLE:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            dateFormat = "DD";
            break;
          case DatabaseUtils.MONTH:
            dateFormat = "MM";
            break;
          case DatabaseUtils.MINUTE:
            dateFormat = "MI";
            break;
          case DatabaseUtils.HOUR:
            dateFormat = "HH";
            break;
          case DatabaseUtils.YEAR:
            dateFormat = "YYYY";
            break;
          default:
            return null;
        }
        truncSQL = "trunc(" + dateColumn + ",'" + dateFormat + "')";
        break;
      case DatabaseUtils.DB2:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            truncSQL = "DATE(" + dateColumn + ") ";
            break;
          default:
            return null;
        }
        break;
      case DatabaseUtils.FIREBIRD:
      case DatabaseUtils.INTERBASE:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            truncSQL = "CAST(" + dateColumn + " AS date) ";
            break;
          default:
            return null;
        }

        break;
      case DatabaseUtils.MYSQL:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            truncSQL = "CAST(" + dateColumn + " AS date) ";
            break;
          default:
            return null;
        }
        break;
      case DatabaseUtils.DERBY:
        switch (truncTo) {
          case DatabaseUtils.DAY:
            truncSQL = "CAST(" + dateColumn + " AS date) ";
            break;
          default:
            return null;
        }
        break;
    }

    return truncSQL;
  }

  
  public static int getMaxId(Connection db, String origTableName, String origSequenceName, String uniqueField) throws SQLException {
    //TODO: Verify the working of this method for all supported databases
    int typeId = DatabaseUtils.getType(db);

    int id = -1;
    Statement st = db.createStatement();
    ResultSet rs = null;
    String sequenceName = getSequenceName(db, origSequenceName);
    String tableName = origTableName;

    switch (typeId) {
      case DatabaseUtils.POSTGRESQL:
        rs = st.executeQuery(
            "SELECT nextval('" + sequenceName + "')");
        break;
      case DatabaseUtils.MSSQL:
      case DatabaseUtils.DERBY:
        rs = st.executeQuery(
            "SELECT MAX(" + uniqueField + ") FROM " + parseReservedWord(db, tableName));
        break;
      case DatabaseUtils.FIREBIRD:
        rs = st.executeQuery(
            "SELECT GEN_ID (" + sequenceName + ",1) FROM RDB$DATABASE");
        break;
      case DatabaseUtils.DAFFODILDB:
        rs = st.executeQuery("SELECT " + sequenceName + ".nextval from dual");
        break;
      case DatabaseUtils.ORACLE:
        rs = st.executeQuery("SELECT " + sequenceName + ".nextval from dual");
        break;
      case DatabaseUtils.DB2:
        rs = st.executeQuery("VALUES NEXTVAL FOR " + sequenceName);
        break;
      default:
        break;
    }
    if (rs.next()) {
      id = rs.getInt(1);
    }
    rs.close();
    st.close();
    return id;
  }

  
  public static ResultSet executeQuery(Connection db, PreparedStatement pst) throws SQLException{
    return executeQuery(db, pst, null);
  }

  
  public static ResultSet executeQuery(Connection db, PreparedStatement pst, PagedListInfo pagedListInfo) throws SQLException{
    ResultSet rs = null;
    long milies = System.currentTimeMillis();
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, pst);
    }
	System.setProperty("debugsql",">>"+DatabaseUtils.class.getName());
    rs = pst.executeQuery();
	//System.setProperty("debugsql","false");
    milies = System.currentTimeMillis() - milies;
    log.debug(pst);
    log.debug(milies + " ms.");
    if (milies > POSSIBLE_QUERY_TIME) {
      log.warn("To improve the speed of your application please send the following query to Fumine CRM support:");
      log.warn("------------------------");
      log.warn(pst);
      log.warn("[QUERY TIME: " + milies + " ms.]");
      log.warn("------------------------");
    }
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, rs);
    }
    return rs;
  }

	public static ResultSet executeQuery(Connection db, PreparedStatement pst, PagedListInfo pagedListInfo,String dsqlstr) throws SQLException{
    ResultSet rs = null;
    long milies = System.currentTimeMillis();
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, pst);
    }
	System.setProperty("debugsql",dsqlstr);
    rs = pst.executeQuery();
	//System.setProperty("debugsql","false");
    milies = System.currentTimeMillis() - milies;
    log.debug(pst);
    log.debug(milies + " ms.");
    if (milies > POSSIBLE_QUERY_TIME) {
      log.warn("To improve the speed of your application please send the following query to Fumine CRM support:");
      log.warn("------------------------");
      log.warn(pst);
      log.warn("[QUERY TIME: " + milies + " ms.]");
      log.warn("------------------------");
    }
    if (pagedListInfo != null) {
      pagedListInfo.doManualOffset(db, rs);
    }
    return rs;
  }
 
  public static String createParams(ArrayList list,String column,Connection db,String option,String dataType) throws SQLException{
    String qStr = null;
    StringBuffer str = null;
    if(MVCConstant.STRING.equals(dataType.toLowerCase())){
  	    qStr = DatabaseUtils.toLowerCase(db) + column + " LIKE " + DatabaseUtils.toLowerCase(db) + " (?) ";
  	}else if(MVCConstant.INT.equals(dataType.toLowerCase())){
  		qStr = column + " = ? ";
  	}else{
		throw new SQLException("DatabaseUtils.java -> In createParams() for datatType parameter only MVCConstant.INT or MVCConstant.STRING can be used");
  	}
	str = new StringBuffer(" ( "+ qStr);
  for(int count=1;count<list.size();count++){
  	if(option.equals(MVCConstant.ALL)){
  		str.append(" AND ");
  	}
  	else{
  		str.append(" OR ");
  	}
  	str.append(qStr);
   }
    str.append(" ) ");
	return str.toString();
  }

  
   public static String createParams(ArrayList list,String column){
   String qStr = column;
   StringBuffer str = new StringBuffer(" ( "+ qStr);
   for(int count=1;count<list.size();count++){
  	str.append(" INTERSECT ");
  	str.append(qStr);
   }
    str.append(" ) ");
    return str.toString();
 }
  public static ConnectionElement createConnectionElement(){
    String gkDriver = MVCConfig.getConfig().get("gatekeeper.driver");
    String gkHost = MVCConfig.getConfig().get("gatekeeper.url");
    String gkUser = MVCConfig.getConfig().get("gatekeeper.user");
    String gkUserPw = MVCConfig.getConfig().get("gatekeeper.password");

	ConnectionElement ce = new ConnectionElement(gkHost, gkUser, gkUserPw);
	ce.setDbName(MVCConfig.getConfig().get("gatekeeper.database"));
	ce.setDriver(gkDriver);	
	return ce;
  }
	public static synchronized Connection getConnection() throws SQLException {

		return DriverManager.getConnection(
			MVCConfig.getConfig().get("gatekeeper.url"),
			MVCConfig.getConfig().get("gatekeeper.user"),
			MVCConfig.getConfig().get("gatekeeper.password")
		);
	}
	public static String getDateTimeString(int d0){
		int d2=d0/3600;
		d0=d0%3600;
		int d3=d0/60;
		int d4=d0%60;
		return String.format("%s:%02d:%02d", d2,d3,d4);
	}
	public static Map createFunction(Map hqlMap){
	  if (getDrive(MVCConfig.getConfig().get("gatekeeper.driver")) == MSSQL){
		  hqlMap.put("ifnull","isnull");
	  }else{
		  hqlMap.put("ifnull","ifnull");
	  }
	  return hqlMap;
	}
}
